package com.jy.dao;

import com.jy.bean.ShoppingTrolley;
import com.jy.util.DBUtil;
import org.omg.PortableInterceptor.INACTIVE;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ShoppingTrolleyDao {
    private static final Logger log = LoggerFactory.getLogger(UserDao.class);

    /**
     * 给购物车页面添加一条数据
     * @param st
     * @return
     */
    public boolean register(ShoppingTrolley st){
        boolean flag = false;
        String sql = "insert into m_shoppingTrolley (user_id,consignee_id,productName,price,quantity) values(?,?,?,?,?)";
        log.info("register:"+sql);
        Connection conn = null;
        PreparedStatement ps = null;
        conn = DBUtil.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1, st.getUser_id());
            ps.setInt(2, st.getConsignee_id());
            ps.setString(3,st.getProductName());
            ps.setInt(4,st.getPrice());
            ps.setInt(5,st.getQuantity());
            int co = ps.executeUpdate();
            if (co>0){
                flag=true;
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn, ps, null);
        }
        return flag;
    }

    /**
     * 查询当前账号的购物车的数量
     * @param name
     * @return
     */
    public Integer selectByName(String name){
        Integer count = 0;
        String sql = "SELECT COUNT(*) from m_user as c INNER JOIN m_shoppingtrolley as d ON c.user_id = d.user_id WHERE c.user_name=?";
        log.info("selectByName:"+sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = DBUtil.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();
            while (rs.next()){
                count = rs.getInt(1);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn, ps, null);
        }
        return count;
    }

    /**
     * 查询当前账号的加入购物车的商品，购物车是否存在
     * @param user_id
     * @param consignee_id
     * @return
     */
    public Integer selectQuantity(Integer user_id,Integer consignee_id){
        Integer quantity = 0;
        String sql = "SELECT d.quantity from m_user as c INNER JOIN m_shoppingtrolley as d ON c.user_id = d.user_id WHERE d.user_id=? AND d.consignee_id=?";
        log.info("selectByName:"+sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = DBUtil.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1, user_id);
            ps.setInt(2,consignee_id);
            rs = ps.executeQuery();
            while (rs.next()){
                quantity = rs.getInt(1);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn, ps, null);
        }
        return quantity;
    }

    /**
     * 把查询到的当前商品的购物车数量，修改并加1
     * @param st
     * @return
     */
    public Boolean updateQuantity(ShoppingTrolley st){
        Boolean favg = false;
        String sql = "UPDATE m_shoppingtrolley SET quantity = ? WHERE user_id=? AND consignee_id=?";
        log.info(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,st.getQuantity());
            ps.setInt(2,st.getUser_id());
            ps.setInt(3,st.getConsignee_id());
            favg = ps.executeUpdate()>0;
        }catch (Exception e){

        }finally {
            DBUtil.closeAll(conn,ps,null);
        }
        return favg;
    }

    /**
     * 根据页面传过来的商品名称来删除购物车的该信息
     * @param name
     * @return
     */
    public Boolean deleteByName(String name){
        Boolean favg = false;
        String sql = "DELETE FROM m_shoppingtrolley WHERE productName=?";
        log.info(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            favg = ps.executeUpdate()>0;
        }catch (Exception e){

        }finally {
            DBUtil.closeAll(conn,ps,null);
        }
        return favg;
    }

    /**
     * 查询当前用户购物车所有的商品
     * @param name
     * @return
     */
    public List<ShoppingTrolley> selectByUsername(String name){
        List<ShoppingTrolley> stList = new ArrayList<>();
        String sql = "SELECT d.productName,d.price,d.quantity from m_user as c INNER JOIN m_shoppingtrolley as d ON c.user_id = d.user_id WHERE c.user_name=?";
        log.info("selectByName:"+sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = DBUtil.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setString(1, name);
            rs = ps.executeQuery();
            while (rs.next()){
                ShoppingTrolley st = new ShoppingTrolley();
                st.setProductName(rs.getString(1));
                st.setPrice(rs.getInt(2));
                st.setQuantity(rs.getInt(3));
                stList.add(st);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn, ps, null);
        }
        return stList;
    }

    /**
     * 根据页面传过来的用户id来删除购物车的该信息
     * @param id
     * @return
     */
    public Boolean deleteById(Integer id){
        Boolean favg = false;
        String sql = "DELETE FROM m_shoppingtrolley WHERE user_id=?";
        log.info(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            favg = ps.executeUpdate()>0;
        }catch (Exception e){

        }finally {
            DBUtil.closeAll(conn,ps,null);
        }
        return favg;
    }

    /**
     * 根据商品名称修改该商品的购物车数量
     * @param productName
     * @param amount
     * @return
     */
    public Boolean updateByproductNameQuantity(String productName,String amount){
        Boolean favg = false;
        String sql = "UPDATE m_shoppingtrolley SET quantity = ? WHERE productName=?";
        log.info(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,amount);
            ps.setString(2,productName);
            favg = ps.executeUpdate()>0;
        }catch (Exception e){

        }finally {
            DBUtil.closeAll(conn,ps,null);
        }
        return favg;
    }

    /**
     * 查询当前用户id购物车的用户id（user_id）
     * 		商品id（consignee_id）
     * 		订单应付金额（payable）
     * 		商品购买数量（quantity）
     * @param id
     * @return
     */
    public List<ShoppingTrolley> selectByUserId(Integer id){
        List<ShoppingTrolley> stList = new ArrayList<>();
        String sql = "SELECT user_id,consignee_id,price,quantity FROM m_shoppingtrolley WHERE user_id=?";
        log.info("selectByName:"+sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        conn = DBUtil.getConnection();
        try{
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()){
                ShoppingTrolley st = new ShoppingTrolley();
                st.setUser_id(rs.getInt(1));
                st.setConsignee_id(rs.getInt(2));
                st.setPrice(rs.getInt(3));
                st.setQuantity(rs.getInt(4));
                stList.add(st);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn, ps, null);
        }
        log.debug("stList: "+stList);
        return stList;
    }

//    public static void main(String[] args) {
//        Integer quantity = ShoppingTrolleyDao.selectQuantity(34,10);
//        log.info(String.valueOf(quantity));
//        if (ShoppingTrolleyDao.updateQuantity(new ShoppingTrolley(null,34,10,null,null,quantity+1))){
//            log.info("修改成功");
//        }else {
//            log.info("修改失败");
//        }
//    }
}
